package com.global.gbinventory.barang.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBMaster {
	
	private Connection connection;

	private PreparedStatement insertMerek;
	private PreparedStatement updateMerek;
	private PreparedStatement deleteMerek;
	private PreparedStatement queryMerek;
	private PreparedStatement queryLastPkMerek;
	
	private PreparedStatement insertSatuan;
	private PreparedStatement updateSatuan;
	private PreparedStatement deleteSatuan;
	private PreparedStatement querySatuan;
	private PreparedStatement queryLastPkSatuan;
	
	private PreparedStatement insertGudang;
	private PreparedStatement updateGudang;
	private PreparedStatement deleteGudang;
	private PreparedStatement queryGudang;
	private PreparedStatement queryLastPkGudang;
	
	private PreparedStatement insertGrup;
	private PreparedStatement updateGrup;
	private PreparedStatement deleteGrup;
	private PreparedStatement queryGrup;
	private PreparedStatement queryLastPkGrup;
	
	private PreparedStatement insertSubgrup;
	private PreparedStatement updateSubgrup;
	private PreparedStatement deleteSubgrup;
	private PreparedStatement querySubgrup;
	private PreparedStatement queryLastPkSubgrup;
	
	private PreparedStatement insertKaryawan;
	private PreparedStatement updateKaryawan;
	private PreparedStatement deleteKaryawan;
	private PreparedStatement queryKaryawan;
	private PreparedStatement queryLastPkKaryawan;
	
	private PreparedStatement insertPelanggan;
	private PreparedStatement updatePelanggan;
	private PreparedStatement deletePelanggan;
	private PreparedStatement queryPelanggan;
	private PreparedStatement queryLastPkPelanggan;
	
	private PreparedStatement insertSupplier;
	private PreparedStatement updateSupplier;
	private PreparedStatement deleteSupplier;
	private PreparedStatement querySupplier;
	private PreparedStatement queryLastPkSupplier;
	
	public DBMaster(){
		this.connectToDB();
		this.prepareStatement();
	}
	
	private void connectToDB(){

		try {
			//load the sqlite-JDBC driver using the current class loader
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(CONSTANT.DB_URL);
			
			System.out.println("Database connection established");

		} catch (SQLException e) {
			System.err.println("Cannot connect to database server");
			e.printStackTrace();
		} catch (ClassNotFoundException e){
			e.printStackTrace();
		}
	}
	
	private void prepareStatement(){
		if(connection != null){
			try {
				connection.setAutoCommit(false);
				
				//Merek
				insertMerek = connection
						.prepareStatement("INSERT INTO Merek(nama) "
								+ "VALUES(?);");
				updateMerek = connection
						.prepareStatement("UPDATE Merek "
								+ "SET nama = ? " 
								+ "WHERE id = ?;");
				deleteMerek = connection
						.prepareStatement("DELETE FROM Merek WHERE id = ?;");
				queryMerek = connection.prepareStatement("SELECT * FROM Merek;");
				queryLastPkMerek = connection.prepareStatement("SELECT MAX(id) FROM Merek;");
				
				//Satuan
				insertSatuan = connection
						.prepareStatement("INSERT INTO Satuan(nama) "
								+ "VALUES(?);");
				updateSatuan = connection
						.prepareStatement("UPDATE Satuan "
								+ "SET nama = ? "
								+ "WHERE id = ?;");
				deleteSatuan = connection
						.prepareStatement("DELETE FROM Satuan WHERE id = ?;");
				querySatuan = connection.prepareStatement("SELECT * FROM Satuan;");
				queryLastPkSatuan = connection.prepareStatement("SELECT MAX(id) FROM Satuan;");
				
				//Gudang
				insertGudang = connection
						.prepareStatement("INSERT INTO Gudang(kode, nama, lokasi) "
								+ "VALUES(?, ?, ?);");
				updateGudang = connection
						.prepareStatement("UPDATE Gudang "
								+ "SET kode = ?, nama = ?, lokasi = ? "
								+ "WHERE id = ?;");
				deleteGudang = connection
						.prepareStatement("DELETE FROM Gudang WHERE id = ?;");
				queryGudang = connection.prepareStatement("SELECT * FROM Gudang;");
				queryLastPkGudang = connection.prepareStatement("SELECT MAX(id) FROM Gudang;");
				
				//Group
				insertGrup = connection
						.prepareStatement("INSERT INTO Grup(nama) "
								+ "VALUES(?);");
				updateGrup = connection
						.prepareStatement("UPDATE Grup "
								+ "SET nama = ? " 
								+ "WHERE id = ?;");
				deleteGrup = connection
						.prepareStatement("DELETE FROM Grup WHERE id = ?;");
				queryGrup = connection.prepareStatement("SELECT * FROM Grup;");
				queryLastPkGrup = connection.prepareStatement("SELECT MAX(id) FROM Grup;");
				
				//Sub-Group
				insertSubgrup = connection
						.prepareStatement("INSERT INTO Subgrup(nama, grup) "
								+ "VALUES(?, ?);");
				updateSubgrup = connection
						.prepareStatement("UPDATE Subgrup "
								+ "SET nama = ? " 
								+ "WHERE id = ?;");
				deleteSubgrup = connection
						.prepareStatement("DELETE FROM Subgrup WHERE id = ?;");
				querySubgrup = connection.prepareStatement("SELECT * FROM Subgrup WHERE grup = ?;");
				queryLastPkSubgrup = connection.prepareStatement("SELECT MAX(id) FROM Subgrup;");
				
				//Karyawan
				insertKaryawan = connection
						.prepareStatement("INSERT INTO Karyawan(kode, nama, alamat, kota, kodepos, provinsi, " +
								"telepon, hp, email, keterangan) "
								+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
				updateKaryawan = connection
						.prepareStatement("UPDATE Karyawan "
								+ "SET kode = ?, nama = ?, alamat = ?, kota = ?, kodepos = ?, provinsi = ?" +
								", telepon = ?, hp = ?, email = ?, keterangan = ? "
								+ "WHERE id = ?;");
				deleteKaryawan = connection
						.prepareStatement("DELETE FROM Karyawan WHERE id = ?;");
				queryKaryawan = connection.prepareStatement("SELECT * FROM Karyawan;");
				queryLastPkKaryawan = connection.prepareStatement("SELECT MAX(id) FROM Karyawan;");
				
				//Pelanggan
				insertPelanggan = connection
						.prepareStatement("INSERT INTO Pelanggan(kode, nama, alamat, kota, kodepos, provinsi, " +
								"telepon, hp, email, keterangan) "
								+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
				updatePelanggan = connection
						.prepareStatement("UPDATE Pelanggan "
								+ "SET kode = ?, nama = ?, alamat = ?, kota = ?, kodepos = ?, provinsi = ?" +
								", telepon = ?, hp = ?, email = ?, keterangan = ? "
								+ "WHERE id = ?;");
				deletePelanggan = connection
						.prepareStatement("DELETE FROM Pelanggan WHERE id = ?;");
				queryPelanggan = connection.prepareStatement("SELECT * FROM Pelanggan;");
				queryLastPkPelanggan = connection.prepareStatement("SELECT MAX(id) FROM Pelanggan;");
				
				//Supplier
				insertSupplier = connection
						.prepareStatement("INSERT INTO Supplier(kodesupplier, namasupplier, alamat, kota, kodepos, " +
								"provinsi, telepon, namaagen, hp, email, keterangan) "
								+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
				updateSupplier = connection
						.prepareStatement("UPDATE Supplier "
								+ "SET kodesupplier = ?, namasupplier = ?, alamat = ?, kota = ?, kodepos = ?, " +
								"provinsi = ?, telepon = ?, namaagen = ?, hp = ?, email = ?, keterangan = ? "
								+ "WHERE id = ?;");
				deleteSupplier = connection
						.prepareStatement("DELETE FROM Supplier WHERE id = ?;");
				querySupplier = connection.prepareStatement("SELECT * FROM Supplier;");
				queryLastPkSupplier = connection.prepareStatement("SELECT MAX(id) FROM Supplier;");
				
				connection.commit();
				connection.setAutoCommit(true);
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
	}
	
	private void closeConnection(){
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Inserts a tuple.
	 * 
	 * @param type BARANG_TYPE identifying the table
	 * @param tuple
	 * @return
	 * @throws SQLException
	 */
	public int addNewTuple(TYPE type, Object[] tuple) throws SQLException {
		int result = 0;
		if (type != null && tuple != null) {
			switch (type) {
			case MEREKs:
				result += newMerek(tuple[0]);
				break;
			case SATUANs:
				result += newSatuan(tuple[0]);
				break;
			case GUDANGs:
				result += newGudang(tuple[0], tuple[1], tuple[2]);
				break;
			case GRUPs:
				result += newGrup(tuple[0]);
				break;
			case SUBGRUPs:
				result += newSubgrup(tuple[0], tuple[1]);
				break;
			case KARYAWANs:
				result += newKaryawan(tuple[0], tuple[1], tuple[2], tuple[3], tuple[4], tuple[5], tuple[6],
						tuple[7], tuple[8], tuple[9]);
				break;
			case PELANGGANs:
				result += newPelanggan(tuple[0], tuple[1], tuple[2], tuple[3], tuple[4], tuple[5], tuple[6],
						tuple[7], tuple[8], tuple[9]);
				break;
			case SUPPLIERs:
				result += newSupplier(tuple[0], tuple[1], tuple[2], tuple[3], tuple[4], tuple[5], tuple[6],
						tuple[7], tuple[8], tuple[9], tuple[10]);
				break;
			default:
				result = -1;
				break;
			}
		}
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Merek' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 * 
	 * @param merek
	 *            String
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newMerek(Object merek) throws SQLException {
		int result = 0;
		insertMerek.clearParameters();
		insertMerek.setObject(1, merek);

		result += insertMerek.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Satuan' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 * 
	 * @param satuan
	 *            String
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newSatuan(Object satuan) throws SQLException {
		int result = 0;
		insertSatuan.clearParameters();
		insertSatuan.setObject(1, satuan);

		result += insertSatuan.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Gudang' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 * 
	 * @param kode
	 *            String
	 * @param nama
	 *            String
	 * @param lokasi
	 *            String
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newGudang(Object kode, Object nama, Object lokasi) throws SQLException {
		int result = 0;
		insertGudang.clearParameters();
		insertGudang.setObject(1, kode);
		insertGudang.setObject(2, nama);
		insertGudang.setObject(3, lokasi);

		result += insertGudang.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Grup' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 * 
	 * @param merek
	 *            String
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newGrup(Object nama) throws SQLException {
		int result = 0;
		insertGrup.clearParameters();
		insertGrup.setObject(1, nama);

		result += insertGrup.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Subgrup' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 * 
	 * @param merek
	 *            String
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newSubgrup(Object nama, Object group) throws SQLException {
		int result = 0;
		insertSubgrup.clearParameters();
		insertSubgrup.setObject(1, nama);
		insertSubgrup.setObject(2, group);

		result += insertSubgrup.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Karyawan' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 *
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newKaryawan(Object kode, Object nama, Object alamat, Object kota, 
			Object kodepos, Object provinsi, Object telepon, Object hp, Object email,
			Object keterangan) throws SQLException {
		int result = 0;
		insertKaryawan.clearParameters();
		insertKaryawan.setObject(1, kode);
		insertKaryawan.setObject(2, nama);
		insertKaryawan.setObject(3, alamat);
		insertKaryawan.setObject(4, kota);
		insertKaryawan.setObject(5, kodepos);
		insertKaryawan.setObject(6, provinsi);
		insertKaryawan.setObject(7, telepon);
		insertKaryawan.setObject(8, hp);
		insertKaryawan.setObject(9, email);
		insertKaryawan.setObject(10, keterangan);
		
		result += insertKaryawan.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Pelanggan' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 *
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newPelanggan(Object kode, Object nama, Object alamat, Object kota, 
			Object kodepos, Object provinsi, Object telepon, Object hp, Object email,
			Object keterangan) throws SQLException {
		int result = 0;
		insertPelanggan.clearParameters();
		insertPelanggan.setObject(1, kode);
		insertPelanggan.setObject(2, nama);
		insertPelanggan.setObject(3, alamat);
		insertPelanggan.setObject(4, kota);
		insertPelanggan.setObject(5, kodepos);
		insertPelanggan.setObject(6, provinsi);
		insertPelanggan.setObject(7, telepon);
		insertPelanggan.setObject(8, hp);
		insertPelanggan.setObject(9, email);
		insertPelanggan.setObject(10, keterangan);
		
		result += insertPelanggan.executeUpdate();
		return result;
	}
	
	/**
	 * Inserts a tuple into the 'Supplier' table. Parameters are self-explanatory.
	 * Serves as a prototype for other inserts. Using PreparedStatements.
	 *
	 * @return Number of tuples inserted.[expected value = 2]
	 * @throws SQLException
	 */
	private int newSupplier(Object kodesupplier, Object namasupplier, Object alamat, Object kota, 
			Object kodepos, Object provinsi, Object telepon, Object namaagen, Object hp, Object email,
			Object keterangan) throws SQLException {
		int result = 0;
		insertSupplier.clearParameters();
		insertSupplier.setObject(1, kodesupplier);
		insertSupplier.setObject(2, namasupplier);
		insertSupplier.setObject(3, alamat);
		insertSupplier.setObject(4, kota);
		insertSupplier.setObject(5, kodepos);
		insertSupplier.setObject(6, provinsi);
		insertSupplier.setObject(7, telepon);
		insertSupplier.setObject(8, namaagen);
		insertSupplier.setObject(9, hp);
		insertSupplier.setObject(10, email);
		insertSupplier.setObject(11, keterangan);
		
		result += insertSupplier.executeUpdate();
		return result;
	}
	
	/**
	 * Updates a tuple.
	 * 
	 * @param type
	 *            BARANG_TYPE identifying the table
	 * @param tuple
	 * @return
	 * @throws SQLException
	 */
	public int updateTuple(TYPE type, int id, Object[] tuple) throws SQLException {
		int result = 0;
		if (type != null && tuple != null) {
			switch (type) {
			case MEREKs:
				result += updateMerek(id, tuple[0]);
				break;
			case SATUANs:
				result += updateSatuan(id, tuple[0]);
				break;
			case GUDANGs:
				result += updateGudang(id, tuple[0], tuple[1], tuple[2]);
				break;
			case GRUPs:
				result += updateGrup(id, tuple[0]);
				break;
			case SUBGRUPs:
				result += updateSubgrup(id, tuple[0]);
				break;
			case KARYAWANs:
				result += updateKaryawan(id, tuple[0], tuple[1], tuple[2], tuple[3], tuple[4], tuple[5],
						tuple[6], tuple[7], tuple[8], tuple[9]);
				break;
			case PELANGGANs:
				result += updatePelanggan(id, tuple[0], tuple[1], tuple[2], tuple[3], tuple[4], tuple[5],
						tuple[6], tuple[7], tuple[8], tuple[9]);
				break;
			case SUPPLIERs:
				result += updateSupplier(id, tuple[0], tuple[1], tuple[2], tuple[3], tuple[4], tuple[5],
						tuple[6], tuple[7], tuple[8], tuple[9], tuple[10]);
				break;
			default:
				result = -1;
				break;
			}

		}
		return result;
	}

	/**
	 * Updates the tuple specified by id in the 'Merek' table.
	 * 
	 * @param id
	 * @param merek
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateMerek(int id, Object merek) throws SQLException {
		int result = 0;
		updateMerek.clearParameters();
		if (id != -1) {
			updateMerek.setObject(1, merek);
			updateMerek.setInt(2, id);
		
			result = updateMerek.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Satuan' table.
	 * 
	 * @param id
	 * @param satuan
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateSatuan(int id, Object satuan) throws SQLException {
		int result = 0;
		updateSatuan.clearParameters();
		if (id != -1) {
			updateSatuan.setObject(1, satuan);
			updateSatuan.setInt(2, id);
		
			result = updateSatuan.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Gudang' table.
	 * 
	 * @param id
	 * @param kode
	 * @param nama
	 * @param lokasi
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateGudang(int id, Object kode, Object nama, Object lokasi) throws SQLException {
		int result = 0;
		updateGudang.clearParameters();
		if (id != -1) {
			updateGudang.setObject(1, kode);
			updateGudang.setObject(2, nama);
			updateGudang.setObject(3, lokasi);
			updateGudang.setInt(4, id);
		
			result = updateGudang.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Grup' table.
	 * 
	 * @param id
	 * @param merek
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateGrup(int id, Object nama) throws SQLException {
		int result = 0;
		updateGrup.clearParameters();
		if (id != -1) {
			updateGrup.setObject(1, nama);
			updateGrup.setInt(2, id);
		
			result = updateGrup.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Subgrup' table.
	 * 
	 * @param id
	 * @param merek
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateSubgrup(int id, Object nama) throws SQLException {
		int result = 0;
		updateSubgrup.clearParameters();
		if (id != -1) {
			updateSubgrup.setObject(1, nama);
			updateSubgrup.setInt(2, id);
			result = updateSubgrup.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Karyawan' table.
	 * 
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateKaryawan(int id, Object kode, Object nama,  Object alamat, Object kota, Object kodepos, 
			Object provinsi, Object telepon, Object hp, Object email, Object keterangan) throws SQLException {
		int result = 0;
		updateKaryawan.clearParameters();
		if (id != -1) {
			updateKaryawan.setObject(1, kode);
			updateKaryawan.setObject(2, nama);
			updateKaryawan.setObject(3, alamat);
			updateKaryawan.setObject(4, kota);
			updateKaryawan.setObject(5, kodepos);
			updateKaryawan.setObject(6, provinsi);
			updateKaryawan.setObject(7, telepon);
			updateKaryawan.setObject(8, hp);
			updateKaryawan.setObject(9, email);
			updateKaryawan.setObject(10, keterangan);
			updateKaryawan.setInt(11, id);
		
			result = updateKaryawan.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Pelanggan' table.
	 * 
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updatePelanggan(int id, Object kode, Object nama,  Object alamat, Object kota, Object kodepos, 
			Object provinsi, Object telepon, Object hp, Object email, Object keterangan) throws SQLException {
		int result = 0;
		updatePelanggan.clearParameters();
		if (id != -1) {
			updatePelanggan.setObject(1, kode);
			updatePelanggan.setObject(2, nama);
			updatePelanggan.setObject(3, alamat);
			updatePelanggan.setObject(4, kota);
			updatePelanggan.setObject(5, kodepos);
			updatePelanggan.setObject(6, provinsi);
			updatePelanggan.setObject(7, telepon);
			updatePelanggan.setObject(8, hp);
			updatePelanggan.setObject(9, email);
			updatePelanggan.setObject(10, keterangan);
			updatePelanggan.setInt(11, id);
		
			result = updatePelanggan.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Updates the tuple specified by id in the 'Supplier' table.
	 * 
	 * @return Number of rows updated.[expected value = 1]
	 * @throws SQLException
	 */
	private int updateSupplier(int id, Object kodesupplier, Object namasupplier,  Object alamat, Object kota, 
			Object kodepos, Object provinsi, Object telepon, Object namaagen, Object hp, Object email, 
			Object keterangan) throws SQLException {
		int result = 0;
		updateSupplier.clearParameters();
		if (id != -1) {
			updateSupplier.setObject(1, kodesupplier);
			updateSupplier.setObject(2, namasupplier);
			updateSupplier.setObject(3, alamat);
			updateSupplier.setObject(4, kota);
			updateSupplier.setObject(5, kodepos);
			updateSupplier.setObject(6, provinsi);
			updateSupplier.setObject(7, telepon);
			updateSupplier.setObject(8, namaagen);
			updateSupplier.setObject(9, hp);
			updateSupplier.setObject(10, email);
			updateSupplier.setObject(11, keterangan);
			updateSupplier.setInt(12, id);
		
			result = updateSupplier.executeUpdate();
		}
		return result;
	}
	
	/**
	 * Removes a tuple.
	 * 
	 * @param type
	 *            BARANG_TYPE identifying the table
	 * @param id
	 *            primary key of the row
	 * @return
	 * @throws SQLException
	 */
	public int removeTuple(TYPE type, int id) throws SQLException {
		int result = 0;
		if (type != null && id > -1) {
			switch (type) {
			case MEREKs:
				result += removeMerek(id);
				break;
			case SATUANs:
				result += removeSatuan(id);
				break;
			case GUDANGs:
				result += removeGudang(id);
				break;
			case GRUPs:
				result += removeGrup(id);
				break;
			case SUBGRUPs:
				result += removeSubgrup(id);
				break;
			case KARYAWANs:
				result += removeKaryawan(id);
				break;
			case PELANGGANs:
				result += removePelanggan(id);
				break;
			case SUPPLIERs:
				result += removeSupplier(id);
				break;
			default:
				result = -1;
				break;
			}
		}
		return result;
	}
	
	/**
	 * Removes a tuple from the 'Merek' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeMerek(int id) throws SQLException {
		deleteMerek.setInt(1, id);
		return deleteMerek.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Satuan' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeSatuan(int id) throws SQLException {
		deleteSatuan.setInt(1, id);
		return deleteSatuan.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Gudang' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeGudang(int id) throws SQLException {
		deleteGudang.setInt(1, id);
		return deleteGudang.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Grup' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeGrup(int id) throws SQLException {
		deleteGrup.setInt(1, id);
		return deleteGrup.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Subgrup' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeSubgrup(int id) throws SQLException {
		deleteSubgrup.setInt(1, id);
		return deleteSubgrup.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Karyawan' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeKaryawan(int id) throws SQLException {
		deleteKaryawan.setInt(1, id);
		return deleteKaryawan.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Pelanggan' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removePelanggan(int id) throws SQLException {
		deletePelanggan.setInt(1, id);
		return deletePelanggan.executeUpdate();
	}
	
	/**
	 * Removes a tuple from the 'Supplier' table.
	 * 
	 * @param id
	 *            ID of the row to be removed.
	 * @return Number of rows deleted.[expected value = 1or2]
	 * @throws SQLException
	 */
	private int removeSupplier(int id) throws SQLException {
		deleteSupplier.setInt(1, id);
		return deleteSupplier.executeUpdate();
	}
	
	/**
	 * get data from database to be mapped into table model
	 * @param type
	 * @return data from DB
	 */
	public List<Object[]> getData(TYPE type){
		int cols = type.getColumnNames().length + 1; //plus 1 because plus id column
		List<Object[]> data = new ArrayList<Object[]>();
		Object[] tuple = null;
		ResultSet rs = null;
		try{
			switch (type) {
			case MEREKs:
				rs = queryMerek.executeQuery();
				break;
			case SATUANs:
				rs = querySatuan.executeQuery();
				break;
			case GUDANGs:
				rs = queryGudang.executeQuery();
				break;
			case GRUPs:
				rs = queryGrup.executeQuery();
				break;
//			case SUBGRUPs:
//				querySubgrup.clearParameters();
//				
//				rs = querySubgrup.executeQuery();
//				break;
			case KARYAWANs:
				rs = queryKaryawan.executeQuery();
				break;
			case PELANGGANs:
				rs = queryPelanggan.executeQuery();
				break;
			case SUPPLIERs:
				rs = querySupplier.executeQuery();
				break;
			default:
				break;
			}
			
			while(rs.next()){
				tuple = new Object[cols];
				for(int i = 0; i < cols; i++){
					tuple[i] = rs.getObject(i + 1);
				}
				
				data.add(tuple);
			}
			
		} catch(SQLException e){
			e.printStackTrace();
		}
		
		
		return data;
	}
	
	/**
	 * get data from database to be mapped into subgrup table model
	 * @param type
	 * @return data from DB
	 */
	public List<Object[]> getSubgrupData(int grup){
		int cols = TYPE.SUBGRUPs.getColumnNames().length + 1; //plus 1 because plus id column
		List<Object[]> data = new ArrayList<Object[]>();
		Object[] tuple = null;
		ResultSet rs = null;
		try{
			
			querySubgrup.clearParameters();
			if(grup != -1){
				querySubgrup.setInt(1, grup);
				rs = querySubgrup.executeQuery();
			}
			
			while(rs.next()){
				tuple = new Object[cols];
				for(int i = 0; i < cols; i++){
					tuple[i] = rs.getObject(i + 1);
				}
				
				data.add(tuple);
			}
			
		} catch(SQLException e){
			e.printStackTrace();
		}
		
		return data;
	}
	
	/**
	 * method to get the last primary key from database
	 * @param type
	 * @return last primary key from DB
	 */
	public int getLastPkFromDB(TYPE type){
		int pk = -1;
		ResultSet rs = null;
		try{
			switch (type) {
			case MEREKs:
				rs = queryLastPkMerek.executeQuery();
				break;
			case SATUANs:
				rs = queryLastPkSatuan.executeQuery();
				break;
			case GUDANGs:
				rs = queryLastPkGudang.executeQuery();
				break;
			case GRUPs:
				rs = queryLastPkGrup.executeQuery();
				break;
			case SUBGRUPs:
				rs = queryLastPkSubgrup.executeQuery();
				break;
			case KARYAWANs:
				rs = queryLastPkKaryawan.executeQuery();
				break;
			case PELANGGANs:
				rs =queryLastPkPelanggan.executeQuery();
				break;
			case SUPPLIERs:
				rs = queryLastPkSupplier.executeQuery();
				break;
			default:
				break;
			}
			
			while(rs.next()){
				pk = rs.getInt(1);
			}
		} catch(SQLException e){
			e.printStackTrace();
		}
		
		return pk;
	}
	
}
